package com.serviceprovider.business.module.admin.transaction.services.dao.impl;

//~--- non-JDK imports --------------------------------------------------------

import com.serviceprovider.business.module.admin.transaction.model.CSVToFlatTableMapping;
import com.serviceprovider.business.module.admin.transaction.model.CSVToFlatTableMappingData;
import com.serviceprovider.business.module.admin.transaction.services.dao.interfaces.ICSVToFlatTableMappingService;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.jdbc.support.DatabaseMetaDataCallback;
import org.springframework.jdbc.support.JdbcUtils;
import org.springframework.jdbc.support.MetaDataAccessException;
import org.springframework.stereotype.Repository;

//~--- JDK imports ------------------------------------------------------------

import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Repository("csvMappingDataService")
public class CSVToFlatTableMappingServiceImpl extends JdbcDaoSupport implements ICSVToFlatTableMappingService {
    @Autowired
    public CSVToFlatTableMappingServiceImpl(@Qualifier("jdbcTemplate") JdbcTemplate jdbcTemplate) {
        setJdbcTemplate(jdbcTemplate);
    }

    @Override
    public void deleteMappingData(int mappingId) {
        String sql = "delete from field_mappings where id = ?";

        getJdbcTemplate().update(sql, new Object[] { mappingId });
    }

    @Override
    public List<CSVToFlatTableMappingData> listMappingData(int mappingId) {
        String                          sql      = "select *  from field_mappings where mapping_name_id=?";
        List                            list     = getJdbcTemplate().queryForList(sql, new Object[] { mappingId });
        Map                             row      = null;
        List<CSVToFlatTableMappingData> mappings = new ArrayList<CSVToFlatTableMappingData>();

        for (Object obj : list) {
            row = (Map) obj;

            CSVToFlatTableMappingData mapping = new CSVToFlatTableMappingData();

            mapping.setId((Integer) row.get("id"));
            mapping.setMappingId((Integer) row.get("mapping_name_id"));
            mapping.setCsvFileField((String) row.get("csvfile_field_name"));
            mapping.setFlatTableField((String) row.get("flat_table_field_name"));
            mappings.add(mapping);
        }

        return mappings;
    }

    @Override
    public int saveMappingData(CSVToFlatTableMappingData mapping) {
        final String sql =
            "insert into field_mappings (mapping_name_id,csvfile_field_name,flat_table_field_name) values(?,?,?)";

        getJdbcTemplate().update(sql, new Object[] { mapping.getMappingId(), mapping.getCsvFileField(),
                mapping.getFlatTableField() });

        return getJdbcTemplate().queryForInt("select max(id) from field_mappings");
    }

    @Override
    public void updateMappingData(CSVToFlatTableMappingData mapping) {}

    @Override
    public void deleteMapping(int mappingId) {
        String sql = "delete from field_mapping_names where id=?";

        getJdbcTemplate().update(sql, new Object[] { mappingId });
    }

    @Override
    public List<CSVToFlatTableMapping> listMappings() {
        String                      sql      = "select *  from field_mapping_names";
        List                        list     = getJdbcTemplate().queryForList(sql);
        Map                         row      = null;
        List<CSVToFlatTableMapping> mappings = new ArrayList<CSVToFlatTableMapping>();

        for (Object obj : list) {
            row = (Map) obj;

            CSVToFlatTableMapping mapping = new CSVToFlatTableMapping();

            mapping.setId((Integer) row.get("id"));
            mapping.setName((String) row.get("name"));
            mapping.setDescription((String) row.get("description"));
            mappings.add(mapping);
        }

        return mappings;
    }

    @Override
    public CSVToFlatTableMapping getMapping(int id) {
        String sql = "select *  from field_mapping_names where id=?";

        return (CSVToFlatTableMapping) getJdbcTemplate().queryForObject(sql, new Object[] { id }, new RowMapper() {
            @Override
            public Object mapRow(ResultSet row, int arg1) throws SQLException {
                CSVToFlatTableMapping mapping = new CSVToFlatTableMapping();

                mapping.setId(row.getInt("id"));
                mapping.setName(row.getString("name"));
                mapping.setDescription(row.getString("description"));

                return mapping;
            }
        });
    }

    @Override
    public int saveMapping(CSVToFlatTableMapping mapping) {
        final String sql = "insert into field_mapping_names (name,description) values(?,?)";

        getJdbcTemplate().update(sql, new Object[] { mapping.getName(), mapping.getDescription() });

        int recordId = getJdbcTemplate().queryForInt("Select max(id) from field_mapping_names");

        return recordId;
    }

    @Override
    public void updateMapping(CSVToFlatTableMapping mapping) {}

    @Override
    public List<String> getFlatTableFields() {
        List<String> columnNames = new ArrayList<String>();

        try {
            columnNames = (ArrayList<String>) JdbcUtils.extractDatabaseMetaData(getJdbcTemplate().getDataSource(),
                    new ColumnNames());
        } catch (MetaDataAccessException e) {
            e.printStackTrace();
        }

        return columnNames;
    }
}


class ColumnNames implements DatabaseMetaDataCallback {
    @Override
    public Object processMetaData(DatabaseMetaData metaData) throws SQLException, MetaDataAccessException {
        ResultSet    rs          = metaData.getColumns(null, null, "newCsvReplica", null);
        List<String> columnNames = new ArrayList<String>();

        while (rs.next()) {
            if (rs.getString("COLUMN_NAME").equalsIgnoreCase("id")
                    || rs.getString("COLUMN_NAME").equalsIgnoreCase("historyId")
                    || rs.getString("COLUMN_NAME").equalsIgnoreCase("mapping_id")
                    || rs.getString("COLUMN_NAME").equalsIgnoreCase("")) {
                continue;
            }

            columnNames.add(rs.getString("COLUMN_NAME"));
        }

        return columnNames;
    }
}



